package name.luojun.DBhelper.utils;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Map.Entry;

import name.luojun.DBhelper.iface.DriverSupport;
import name.luojun.DBhelper.model.ForeignKey;
import name.luojun.DBhelper.model.PrimaryKey;
import name.luojun.DBhelper.model.SimpleColumn;
import name.luojun.DBhelper.model.SimpleTable;
import name.luojun.DBhelper.model.UniqueKey;
public class MysqlDriver extends DriverSupport {
	public MysqlDriver(Connection connection) {
		super(connection);
	}
	@Override
	public void createTable(SimpleTable table) {
		StringBuffer strbuf = new StringBuffer();
		strbuf.append("CREATE TABLE " + table.getName() + " ( ");
		for (Entry<String, SimpleColumn> en : table.getColumns().entrySet()) {
			strbuf.append(this.getColumnDesc(en.getValue()) + ",");
		}
		strbuf.deleteCharAt(strbuf.length() - 1);
		strbuf.append(" );");
		this.excute(strbuf.toString());
	}
	@Override
	public void addColumn(SimpleTable table, SimpleColumn column) {
		String sql = "Alter table " + table.getName() + " add " + this.getColumnDesc(column) + ";";
		this.excute(sql);
	}
	@Override
	public void modifyColumn(SimpleTable table, SimpleColumn column, SimpleColumn before) {
		String sql = "Alter table " + table.getName() + " modify " + this.getColumnDesc(column, before) + ";";
		this.excute(sql);
	}
	@Override
	public void dropColumn(SimpleTable table, SimpleColumn column) {
		String sql = "Alter table " + table.getName() + " drop column " + column.getName() + ";";
		this.excute(sql);
	}
	@Override
	public void renameColumn(SimpleTable table, SimpleColumn column, String newname) {
		String sql = "Alter table " + table.getName() + " change " + column.getName() + " to " + this.getColumnDesc(column, newname) + ";";
		this.excute(sql);
	}
	@Override
	public void dropPrimayKey(SimpleTable table) {
		String sql = "Alter table " + table.getName() + " drop primary key;";
		this.excute(sql);
	}
	@Override
	public void addPrimayKey(SimpleTable table) {
		if (table.getPrimarykey() != null) {
			StringBuffer strbuf = new StringBuffer();
			strbuf.append("Alter table " + table.getName() + " add");
			String pkname = table.getPrimarykey().getName();
			if (!this.isStrEmpty(pkname)) {
				strbuf.append(" CONSTRAINT ");
				strbuf.append(pkname);
			}
			strbuf.append(" PRIMARY KEY ( ");
			for (String s : table.getPrimarykey().getColumns()) {
				strbuf.append(s + ",");
			}
			strbuf.deleteCharAt(strbuf.length() - 1);
			strbuf.append(");");
			this.excute(strbuf.toString());
		}
	}
	@Override
	public void dropUniqueKey(SimpleTable table, UniqueKey uniqueKey) {
		StringBuffer strbuf = new StringBuffer();
		strbuf.append("Alter table " + table.getName() + " drop index " + uniqueKey.getName() + ";");
		this.excute(strbuf.toString());
	}
	@Override
	public void addUniqueKey(SimpleTable table, UniqueKey uniqueKey) {
		StringBuffer strbuf = new StringBuffer();
		strbuf.append("Alter table " + table.getName() + " add ");
		if (!this.isStrEmpty(uniqueKey.getName())) {
			strbuf.append(" CONSTRAINT ");
			strbuf.append(uniqueKey.getName());
		}
		strbuf.append(" unique(");
		for (SimpleColumn column : uniqueKey.getColumns()) {
			strbuf.append(column.getName() + ",");
		}
		strbuf.deleteCharAt(strbuf.length() - 1);
		strbuf.append(");");
		this.excute(strbuf.toString());
	}
	@Override
	public void dropForeignKey(SimpleTable table, ForeignKey foreignKey) {
		StringBuffer strbuf = new StringBuffer();
		strbuf.append("Alter table " + table.getName() + " drop CONSTRAINT " + foreignKey.getName() + ";");
		this.excute(strbuf.toString());
	}
	@Override
	public void addForeignKey(SimpleTable table, ForeignKey foreignKey) {
		StringBuffer strbuf = new StringBuffer();
		strbuf.append("Alter table " + table.getName() + " add ");
		if (!this.isStrEmpty(foreignKey.getName())) {
			strbuf.append(" CONSTRAINT ");
			strbuf.append(foreignKey.getName());
		}
		strbuf.append(" foreign key(");
		for (SimpleColumn column : foreignKey.getColumns()) {
			strbuf.append(column.getName() + ",");
		}
		strbuf.deleteCharAt(strbuf.length() - 1);
		strbuf.append(") references" + foreignKey.getReftable() + " (");
		for (String column : foreignKey.getRefcolumns()) {
			strbuf.append(column + ",");
		}
		strbuf.deleteCharAt(strbuf.length() - 1);
		strbuf.append(");");
		this.excute(strbuf.toString());
	}
	private String getColumnDesc(SimpleColumn column) {
		StringBuffer strbuf = new StringBuffer();
		strbuf.append(column.getName() + " " + column.getType());
		if (column.getLength() > 0) {
			strbuf.append("(" + column.getLength() + ")");
		}
		if (!isStrEmpty(column.getDefaultvalue())) {
			if ("VARCHAR".equalsIgnoreCase(column.getType())) {
				strbuf.append(" DEFAULT '" + column.getDefaultvalue() + "'");
			} else
				strbuf.append(" DEFAULT " + column.getDefaultvalue());
		}
		if (column.getNotnull()) {
			strbuf.append(" not null ");
		}
		if (!isStrEmpty(column.getComment())) {
				strbuf.append(" COMMENT '" + column.getComment() + "'");
		}
		return strbuf.toString();
	}
	private String getColumnDesc(SimpleColumn column, String colname) {
		StringBuffer strbuf = new StringBuffer();
		strbuf.append(colname + " " + column.getType());
		if (column.getLength() > 0) {
			strbuf.append("(" + column.getLength() + ")");
		}
		if (!isStrEmpty(column.getDefaultvalue())) {
			if ("VARCHAR".equalsIgnoreCase(column.getType())) {
				strbuf.append(" DEFAULT '" + column.getDefaultvalue() + "'");
			} else
				strbuf.append(" DEFAULT " + column.getDefaultvalue());
		}
		if (column.getNotnull()) {
			strbuf.append(" not null ");
		}
		return strbuf.toString();
	}
	private String getColumnDesc(SimpleColumn column, SimpleColumn before) {
		StringBuffer strbuf = new StringBuffer();
		strbuf.append(column.getName() + " " + column.getType());
		if (column.getLength() > 0) {
			strbuf.append("(" + column.getLength() + ")");
		}
		if (!isStrEmpty(column.getDefaultvalue())) {
			if ("VARCHAR".equalsIgnoreCase(column.getType())) {
				strbuf.append(" DEFAULT '" + column.getDefaultvalue() + "'");
			} else
				strbuf.append(" DEFAULT " + column.getDefaultvalue());
		}
		if (column.getNotnull()) {
			if (!before.getNotnull())
				strbuf.append(" not null ");
		} else {
			if (before.getNotnull())
				strbuf.append(" null ");
		}
		return strbuf.toString();
	}
	private boolean isStrEmpty(String str) {
		if (str == null) {
			return true;
		}
		if (str.trim().length() == 0) {
			return true;
		}
		return false;
	}
	@Override
	public void addcomment(SimpleTable table, SimpleColumn column) {
		if (isStrEmpty(column.getComment())) {
			return;
		}
		/*
		 * String sql = "ALTER TABLE " + table.getName() + "." + column.getName() + " COMMENT '" + column.getComment() + "';";
		 * this.excute(sql);
		 */
	}
	@Override
	public SimpleTable getTable(String name, String schema) throws SQLException {
		PreparedStatement dbpsmt = connection.prepareStatement("select database()");
		ResultSet db = dbpsmt.executeQuery();
		db.next();
		String dbname = db.getString(1);
		String colsql = "select * from information_schema.columns t where t.table_schema=? and t.table_name=?";
		PreparedStatement psmt = connection.prepareStatement(colsql);
		psmt.setString(1, dbname);
		psmt.setString(2, name);
		ResultSet table = psmt.executeQuery();
		SimpleTable result = new SimpleTable();
		result.setName(name);
		while (table.next()) {
			String columnName = table.getString("COLUMN_NAME").toUpperCase();
			String columnType = table.getString("DATA_TYPE").toUpperCase();
			int datasize = table.getInt("CHARACTER_MAXIMUM_LENGTH");
			String nullable = table.getString("IS_NULLABLE");
			String defaultV = table.getString("COLUMN_DEFAULT");
			if (defaultV != null) {
				defaultV = defaultV.trim();
				if (defaultV.startsWith("'") && defaultV.endsWith("'")) {
					defaultV = defaultV.substring(1, defaultV.length() - 1);
				}
			}
			String comment = table.getString("COLUMN_COMMENT");
			SimpleColumn col = new SimpleColumn(columnName, columnType, datasize, "NO".equalsIgnoreCase(nullable) ? true : false, defaultV,
					comment);
			result.setColumn(col);
		}
		String pksql = "select t.COLUMN_NAME,t.CONSTRAINT_NAME from information_schema.KEY_COLUMN_USAGE t where t.table_schema=? and t.table_name=? and t.CONSTRAINT_NAME='PRIMARY';";
		PreparedStatement pkpsmt = connection.prepareStatement(pksql);
		pkpsmt.setString(1, dbname);
		pkpsmt.setString(2, name);
		ResultSet pkrs = pkpsmt.executeQuery();
		if (pkrs.next()) {
			PrimaryKey pk = new PrimaryKey();
			String pkname = pkrs.getString("CONSTRAINT_NAME").toUpperCase();
			String col = pkrs.getString("COLUMN_NAME").toUpperCase();
			pk.setName(pkname);
			pk.addColumn(col);
			while (pkrs.next()) {
				String colname = pkrs.getString("COLUMN_NAME").toUpperCase();
				pk.addColumn(colname);
			}
			result.setPrimarykey(pk);
		}
		// String
		// fksql="select distinct (ucc.column_name) column_name, rela.table_name, rela.column_name column_name1 from user_constraints uc, user_cons_columns ucc, (select t2.table_name, t2.column_name, t1.r_constraint_name from user_constraints t1, user_cons_columns t2 where t1.r_constraint_name = t2.constraint_name and t1.table_name = ?) rela where uc.constraint_name = ucc.constraint_name and uc.r_constraint_name = rela.r_constraint_name and uc.table_name = ?";
		return result;
	}
}
